package runze.util.io;

import java.io.File;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;

/**
 * 用来导入excel文件，读取其中内容，取出数据处理
 * @author wrzhxy@qq.com
 * @创建时间 2017年5月30日 上午11:15:13
 */
public class ExcelUtil {
	public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException {
		// 读取一个excel
		File file = new File("D:/456.xlsx");
		Workbook wb = WorkbookFactory.create(file);
		
		getCellContent(wb);
	}
	
	public static void getCellContent(Workbook wb) {
		DataFormatter formatter = new DataFormatter();
	    Sheet sheet1 = wb.getSheetAt(0);
	    for (Row row : sheet1) {
	        for (Cell cell : row) {
	            CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
	            System.out.print(cellRef.formatAsString());	// 表格名 A1,F5, etc...
	            System.out.print(" - ");

	            // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
	            String text = formatter.formatCellValue(cell);
	            System.out.print("\t" + text);

	            // Alternatively, get the value and format it yourself
	            switch (cell.getCellTypeEnum()) {
	                case STRING:
	                    System.out.print("\t" + cell.getRichStringCellValue().getString());
	                    break;
	                case NUMERIC:
	                    if (DateUtil.isCellDateFormatted(cell)) {
	                        System.out.print("\t" + cell.getDateCellValue());
	                    } else {
	                        System.out.print("\t" + cell.getNumericCellValue());
	                    }
	                    break;
	                case BOOLEAN:
	                    System.out.print("\t" + cell.getBooleanCellValue());
	                    break;
	                case FORMULA:
	                    System.out.print("\t" + cell.getCellFormula());
	                    break;
	                case BLANK:
	                    System.out.print("-\t");
	                    break;
	                default:
	                    System.out.println();
	            }
	        }
	        System.out.println();
	    }
	}
}
